<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  
  <title>linux安装mysql主从模式 | 清峰小栈</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  
  <meta name="keywords" content="mysql" />
  
  
  
  
  <meta name="description" content="Mysql 双主从(已测试)前提：两个数据库数据一致，没有服务在做写入操作。 一、添加配置文件master1和master2二台服务器，分别到&#x2F;etc&#x2F;my.cnf配置文件， 在mysqld里添加一下属性 123456789101112131415161718192021222324#master1上[mysqld]server-id&#x3D;101log-bin &#x3D; mysql-bi">
<meta property="og:type" content="article">
<meta property="og:title" content="Linux安装mysql主从模式">
<meta property="og:url" content="http://example.com/2021/02/08/Mysql/Linux%E5%AE%89%E8%A3%85mysql%E4%B8%BB%E4%BB%8E%E6%A8%A1%E5%BC%8F/index.html">
<meta property="og:site_name" content="清峰小栈">
<meta property="og:description" content="Mysql 双主从(已测试)前提：两个数据库数据一致，没有服务在做写入操作。 一、添加配置文件master1和master2二台服务器，分别到&#x2F;etc&#x2F;my.cnf配置文件， 在mysqld里添加一下属性 123456789101112131415161718192021222324#master1上[mysqld]server-id&#x3D;101log-bin &#x3D; mysql-bi">
<meta property="og:locale" content="en_US">
<meta property="og:image" content="http://img.lindaifeng.vip/typora-picgo-tuchaung/20230504090436.png">
<meta property="og:image" content="http://img.lindaifeng.vip/typora-picgo-tuchaung/20230504090454.jpg">
<meta property="og:image" content="http://img.lindaifeng.vip/typora-picgo-tuchaung/20230504090635.png">
<meta property="og:image" content="http://img.lindaifeng.vip/typora-picgo-tuchaung/20230504090806.jpg">
<meta property="og:image" content="http://img.lindaifeng.vip/typora-picgo-tuchaung/20230504090816.jpg">
<meta property="article:published_time" content="2021-02-08T01:19:29.000Z">
<meta property="article:modified_time" content="2023-05-04T01:11:58.583Z">
<meta property="article:author" content="清峰">
<meta property="article:tag" content="mysql">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="http://img.lindaifeng.vip/typora-picgo-tuchaung/20230504090436.png">
  

  

  <link rel="icon" href="/qingfeng-blog/css/images/mylogo-2.png">
  <link rel="apple-touch-icon" href="/qingfeng-blog/css/images/mylogo-2.png">
  
    <link href="//fonts.googleapis.com/css?family=Source+Code+Pro" rel="stylesheet" type="text/css">
  
  <link href="https://fonts.googleapis.com/css?family=Open+Sans|Montserrat:700" rel="stylesheet" type="text/css">
  <link href="https://fonts.googleapis.com/css?family=Roboto:400,300,300italic,400italic" rel="stylesheet" type="text/css">
  <!-- <link href="//netdna.bootstrapcdn.com/font-awesome/4.0.3/css/font-awesome.css" rel="stylesheet"> -->
  <link href="/qingfeng-blog/css/font-awesome.css" rel="stylesheet">
  <style type="text/css">
    @font-face{font-family:futura-pt; src:url("/qingfeng-blog/css/fonts/FuturaPTBold.otf") format("woff");font-weight:500;font-style:normal;}
    @font-face{font-family:futura-pt-light; src:url("/qingfeng-blog/css/fonts/FuturaPTBook.otf") format("woff");font-weight:lighter;font-style:normal;}
    @font-face{font-family:futura-pt-italic; src:url("/qingfeng-blog/css/fonts/FuturaPTBookOblique.otf") format("woff");font-weight:400;font-style:italic;}
}

  </style>
  
<link rel="stylesheet" href="/qingfeng-blog/css/style.css">


  
<script src="/qingfeng-blog/js/jquery-3.1.1.min.js"></script>

  
<script src="/qingfeng-blog/js/bootstrap.js"></script>


  <!-- Bootstrap core CSS -->
  <link rel="stylesheet" href="/qingfeng-blog/css/bootstrap.css" >

  
    
<link rel="stylesheet" href="/qingfeng-blog/css/dialog.css">

  

  

  
    <link rel="stylesheet" href="/qingfeng-blog/css/header-post.css" >
  

  
  
  
    <link rel="stylesheet" href="/qingfeng-blog/css/vdonate.css" >
  

<meta name="generator" content="Hexo 6.3.0"></head>



  <body data-spy="scroll" data-target="#toc" data-offset="50">


  
  <div id="container">
    <div id="wrap">
      
        <header>

    <div id="allheader" class="navbar navbar-default navbar-static-top" role="navigation">
        <div class="navbar-inner">
          
          <div class="container"> 
            <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
              <span class="sr-only">Toggle navigation</span>
              <span class="icon-bar"></span>
              <span class="icon-bar"></span>
              <span class="icon-bar"></span>
            </button>

            
              <a class="brand" style="
                 border-width: 0px;  margin-top: 0px;"  
                href="#" data-toggle="modal" data-target="#myModal" >
                  <img width="155px" height="90px" alt="Hike News" src="/qingfeng-blog/css/images/mylogo-2.png">
              </a>
            
            
            <div class="navbar-collapse collapse">
              <ul class="hnav navbar-nav">
                
                  <li> <a class="main-nav-link" href="/qingfeng-blog/">首页</a> </li>
                
                  <li> <a class="main-nav-link" href="/qingfeng-blog/categories">分类</a> </li>
                
                  <li> <a class="main-nav-link" href="/qingfeng-blog/tags">标签</a> </li>
                
                  <li><div id="search-form-wrap">

    <form class="search-form">
        <input type="text" class="ins-search-input search-form-input" placeholder="" />
        <button type="submit" class="search-form-submit"></button>
    </form>
    <div class="ins-search">
    <div class="ins-search-mask"></div>
    <div class="ins-search-container">
        <div class="ins-input-wrapper">
            <input type="text" class="ins-search-input" placeholder="Type something..." />
            <span class="ins-close ins-selectable"><i class="fa fa-times-circle"></i></span>
        </div>
        <div class="ins-section-wrapper">
            <div class="ins-section-container"></div>
        </div>
    </div>
</div>
<script>
(function (window) {
    var INSIGHT_CONFIG = {
        TRANSLATION: {
            POSTS: 'Posts',
            PAGES: 'Pages',
            CATEGORIES: 'Categories',
            TAGS: 'Tags',
            UNTITLED: '(Untitled)',
        },
        ROOT_URL: '/qingfeng-blog/',
        CONTENT_URL: '/qingfeng-blog/content.json',
    };
    window.INSIGHT_CONFIG = INSIGHT_CONFIG;
})(window);
</script>

<script src="/qingfeng-blog/js/insight.js"></script>


</div></li>
            </div>
          </div>
                
      </div>
    </div>

</header>



      
            
      <div id="content" class="outer">
        
          <section id="main" style="float:none;"><article id="post-Mysql/Linux安装mysql主从模式" style="width: 75%; float:left;" class="article article-type-post" itemscope itemprop="blogPost" >
<div id="container">
  <div id="articleInner" class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 class="thumb" class="article-title" itemprop="name">
      Linux安装mysql主从模式
    </h1>
  

      </header>
    
    <div class="article-meta">
      
	<a href="/qingfeng-blog/2021/02/08/Mysql/Linux%E5%AE%89%E8%A3%85mysql%E4%B8%BB%E4%BB%8E%E6%A8%A1%E5%BC%8F/" class="article-date">
	  <time datetime="2021-02-08T01:19:29.000Z" itemprop="datePublished">2021-02-08</time>
	</a>

      
    <a class="article-category-link" href="/qingfeng-blog/categories/Linux/">Linux</a>

      
	<a class="article-views">
	<span id="busuanzi_container_page_pv">
		PV:<span id="busuanzi_value_page_pv"></span>
	</span>
	</a>

      

    </div>
    <div class="article-entry" itemprop="articleBody">
      
        <h2 id="Mysql-双主从-已测试"><a href="#Mysql-双主从-已测试" class="headerlink" title="Mysql 双主从(已测试)"></a>Mysql 双主从(已测试)</h2><p><strong>前提：两个数据库数据一致，没有服务在做写入操作。</strong></p>
<h3 id="一、添加配置文件"><a href="#一、添加配置文件" class="headerlink" title="一、添加配置文件"></a>一、添加配置文件</h3><p>master1和master2二台服务器，分别到&#x2F;etc&#x2F;my.cnf配置文件， 在mysqld里添加一下属性</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br></pre></td><td class="code"><pre><span class="line">#master1上</span><br><span class="line"></span><br><span class="line">[mysqld]</span><br><span class="line"></span><br><span class="line">server-id=101</span><br><span class="line"></span><br><span class="line">log-bin = mysql-bin</span><br><span class="line"></span><br><span class="line">auto-increment-increment = 2</span><br><span class="line"></span><br><span class="line">auto-increment-offset = 1000</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">#master2上</span><br><span class="line"></span><br><span class="line">[mysqld]</span><br><span class="line"></span><br><span class="line">server-id=103</span><br><span class="line"></span><br><span class="line">log-bin = mysql-bin</span><br><span class="line"></span><br><span class="line">auto-increment-increment = 2</span><br><span class="line"></span><br><span class="line">auto-increment-offset = 1001</span><br></pre></td></tr></table></figure>

<h3 id="二、创建用户"><a href="#二、创建用户" class="headerlink" title="二、创建用户"></a>二、创建用户</h3><p>进入Mysql，分别为二台master创建各种的用户供对方使用</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">#master1的机器上</span><br><span class="line">mysql&gt; CREATE USER &#x27;master1&#x27;@&#x27;%&#x27; IDENTIFIED BY &#x27;123456&#x27;;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">#master2的机器上</span><br><span class="line">mysql&gt; CREATE USER &#x27;master2&#x27;@&#x27;%&#x27; IDENTIFIED BY &#x27;123456&#x27;;</span><br></pre></td></tr></table></figure>

<p> <strong>(1819错误密码长度限制问题：set global validate_password_policy&#x3D;0;)</strong></p>
<h3 id="三、分别给用户授予复制权限"><a href="#三、分别给用户授予复制权限" class="headerlink" title="三、分别给用户授予复制权限"></a>三、分别给用户授予复制权限</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">#master1的机器上</span><br><span class="line">mysql&gt; GRANT REPLICATION SLAVE  ON *.* TO &#x27;master1&#x27;@&#x27;%&#x27; IDENTIFIED BY &#x27;123456&#x27;;</span><br><span class="line"></span><br><span class="line">刷新</span><br><span class="line">Mysql&gt;flush privileges;</span><br><span class="line"></span><br><span class="line">#master2的机器上</span><br><span class="line">mysql&gt; GRANT REPLICATION SLAVE  ON *.* TO &#x27;master2&#x27;@&#x27;%&#x27; IDENTIFIED BY &#x27;123456&#x27;;</span><br><span class="line"></span><br><span class="line">刷新</span><br><span class="line">Mysql&gt;flush privileges;</span><br><span class="line"></span><br><span class="line"> </span><br></pre></td></tr></table></figure>

<h3 id="四、分别重启服务"><a href="#四、分别重启服务" class="headerlink" title="四、分别重启服务"></a>四、分别重启服务</h3><p>登录mysql用户，通过show master status; 查看二进制文件名称还有pos位置，为slave配置复制位置</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">service mysqld restart;</span><br></pre></td></tr></table></figure>

<p><img src="http://img.lindaifeng.vip/typora-picgo-tuchaung/20230504090436.png" alt="img"></p>
<h3 id="五、配置主从机"><a href="#五、配置主从机" class="headerlink" title="五、配置主从机"></a>五、配置主从机</h3><p>分别切换master，注意master_log_file还有master_log_pos mysql里面操作</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">#master1的机器上</span><br><span class="line"></span><br><span class="line">mysql&gt; change master to master_host=&#x27;192.168.137.103&#x27;,master_user=&#x27;master2&#x27;,master_password=&#x27;123456&#x27;,master_log_file=&#x27;mysql-bin.000003&#x27;,master_log_pos=951;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">#master2的机器上</span><br><span class="line"></span><br><span class="line">mysql&gt; change master to master_host=&#x27;192.168.137.101&#x27;,master_user=&#x27;master1&#x27;,master_password=&#x27;123456&#x27;,master_log_file=&#x27;mysql-bin.000004&#x27;,master_log_pos=698;</span><br></pre></td></tr></table></figure>

<h3 id="七、分别利用命令启动slave"><a href="#七、分别利用命令启动slave" class="headerlink" title="七、分别利用命令启动slave"></a>七、分别利用命令启动slave</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; 	start slave;</span><br><span class="line"></span><br><span class="line">关闭slave</span><br><span class="line">stop slave</span><br></pre></td></tr></table></figure>



<h3 id="八、测试结果"><a href="#八、测试结果" class="headerlink" title="八、测试结果"></a>八、测试结果</h3><p>在master1上创建数据库，master2是否能同步，然后再在master2上创建表写数据，看master1能否同步。</p>
<p><img src="http://img.lindaifeng.vip/typora-picgo-tuchaung/20230504090454.jpg" alt="img"></p>
<h2 id="Mysql-单主从（步骤有问题）"><a href="#Mysql-单主从（步骤有问题）" class="headerlink" title="Mysql 单主从（步骤有问题）"></a>Mysql 单主从（步骤有问题）</h2><h3 id="一、添加配置文件-1"><a href="#一、添加配置文件-1" class="headerlink" title="一、添加配置文件"></a><strong>一、添加配置文件</strong></h3><p>master1和master2二台服务器，修改master1&#x2F;etc&#x2F;my.cnf配置文件， 在mysqld里添加一下属性</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">#master1上</span><br><span class="line"></span><br><span class="line">[mysqld]</span><br><span class="line"></span><br><span class="line">server-id=101</span><br><span class="line"></span><br><span class="line">log-bin = mysql-bin</span><br><span class="line"></span><br><span class="line">auto-increment-increment = 2</span><br><span class="line"></span><br><span class="line">auto-increment-offset = 1000</span><br></pre></td></tr></table></figure>

<h3 id="二、创建用户-1"><a href="#二、创建用户-1" class="headerlink" title="二、创建用户"></a>二、创建用户</h3><p>进入Mysql，创建用户供从库使用</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">#master1的机器上</span><br><span class="line"></span><br><span class="line">mysql&gt; CREATE USER &#x27;master1&#x27;@&#x27;%&#x27; IDENTIFIED BY &#x27;123456&#x27;;</span><br><span class="line"> </span><br></pre></td></tr></table></figure>

<h3 id="三、分别给用户授予复制权限-1"><a href="#三、分别给用户授予复制权限-1" class="headerlink" title="三、分别给用户授予复制权限"></a>三、分别给用户授予复制权限</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">#master1的机器上</span><br><span class="line">mysql&gt; GRANT REPLICATION SLAVE  ON *.* TO &#x27;master1&#x27;@&#x27;%&#x27; IDENTIFIED BY &#x27;123456&#x27;;</span><br><span class="line"></span><br><span class="line">刷新</span><br><span class="line">Mysql&gt;flush privileges;</span><br></pre></td></tr></table></figure>

<h3 id="四、分别重启服务-1"><a href="#四、分别重启服务-1" class="headerlink" title="四、分别重启服务"></a>四、分别重启服务</h3><p>登录mysql用户，通过show master status 查看二进制文件名称还有pos位置，为slave配置复制位置</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">service mysqld restart;</span><br><span class="line"></span><br><span class="line">show master status;</span><br></pre></td></tr></table></figure>

<p><img src="http://img.lindaifeng.vip/typora-picgo-tuchaung/20230504090635.png" alt="img"></p>
<h3 id="五、配置从机"><a href="#五、配置从机" class="headerlink" title="五、配置从机"></a>五、配置从机</h3><p>切换master，注意master_log_file还有master_log_pos mysql里面操作</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">#master2的机器上</span><br><span class="line"></span><br><span class="line">mysql&gt; change master to master_host=&#x27;192.168.232.54&#x27;,master_user=&#x27;master1&#x27;,master_password=&#x27;HNS@gtjy1.0&#x27;,master_log_file=&#x27;mysql-bin.000022&#x27;,master_log_pos=154;</span><br></pre></td></tr></table></figure>

<h3 id="七、利用命令启动slave"><a href="#七、利用命令启动slave" class="headerlink" title="七、利用命令启动slave"></a>七、利用命令启动slave</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; start slave;</span><br><span class="line"> </span><br><span class="line">//关闭slave</span><br><span class="line">stop slave;</span><br></pre></td></tr></table></figure>

<h3 id="八、测试结果-1"><a href="#八、测试结果-1" class="headerlink" title="八、测试结果"></a>八、测试结果</h3><p>在master1上创建数据库，master2是否能同步。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">查看slave状态：</span><br><span class="line">show slave status；</span><br></pre></td></tr></table></figure>

<p><img src="http://img.lindaifeng.vip/typora-picgo-tuchaung/20230504090806.jpg" alt="img">  </p>
<p>安装完成之后，所有mysql重启就可以了 </p>
<p><em><strong>如果是Slave_SQL_Running：no</strong></em></p>
<p><img src="http://img.lindaifeng.vip/typora-picgo-tuchaung/20230504090816.jpg" alt="img"></p>
<p><em><strong>*解决办法如下：*</strong></em></p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">MariaDB [(none)]&gt; stop slave; </span><br><span class="line"></span><br><span class="line">MariaDB [(none)]&gt; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;</span><br><span class="line"></span><br><span class="line">START SLAVE; </span><br><span class="line"></span><br><span class="line">MariaDB [(none)]&gt; start slave; </span><br><span class="line"></span><br><span class="line">MariaDB [(none)]&gt; show slave status;</span><br><span class="line"></span><br><span class="line">show slave status\G;</span><br></pre></td></tr></table></figure>

<h2 id="如果出现Slave-IO-Running-No的机器上操作："><a href="#如果出现Slave-IO-Running-No的机器上操作：" class="headerlink" title="如果出现Slave_IO_Running: No的机器上操作："></a><em><strong>如果出现Slave_IO_Running: No的机器上操作：</strong></em></h2><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">MariaDB [(none)]&gt; stop slave; </span><br><span class="line">MariaDB [(none)]&gt; CHANGE MASTER TO MASTER_LOG_FILE=&#x27;mysql-bin.000026&#x27;,MASTER_LOG_POS=0; MariaDB [(none)]&gt; slave start; </span><br><span class="line">MariaDB [(none)]&gt; show slave status；</span><br></pre></td></tr></table></figure>

<p><strong>删除savlue</strong></p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">stop slave; </span><br><span class="line"></span><br><span class="line">Query OK, 0 rows affected (0.06 sec)</span><br><span class="line"></span><br><span class="line">reset slave;</span><br></pre></td></tr></table></figure>

<p><strong>删除5张表，并重新导入脚本</strong></p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">use mysql</span><br><span class="line"></span><br><span class="line">drop table slave_master_info;</span><br><span class="line"></span><br><span class="line">drop table slave_relay_log_info;</span><br><span class="line"></span><br><span class="line">drop table slave_worker_info;</span><br><span class="line"></span><br><span class="line">drop table innodb_index_stats;</span><br><span class="line"></span><br><span class="line">drop table innodb_table_stats;</span><br><span class="line"></span><br><span class="line">source /usr/coolpad/mysql/share/mysql_system_tables.sql</span><br></pre></td></tr></table></figure>

<p>(2)重新启动数据库</p>
<p>注：这一步是必须的，否则无法正常配置双主架构。</p>
<p>特别注意，数据库主从日志很大</p>
<p>(一般位置为&#x2F;var&#x2F;lib&#x2F;&#x2F;mysql&#x2F;或&#x2F;usr&#x2F;local&#x2F;mysql&#x2F;var&#x2F;）</p>
<p>&lt;2&gt;设置只保留30天的binlog</p>
<p>(临时，重启mysql这个参数会失败)</p>
<p>set global expire_logs_days &#x3D; 30;</p>
<p>(永久，my.cnf中添加，重启后生效)、</p>
<p>expire_logs_days &#x3D; 30</p>
<p>查看当前的日志保存天数</p>
<p>show variables like ‘expire_logs_days’;</p>
<p> 数据库重启后，mysql主动配置也需要重启。</p>

      
    </div>
    <footer class="article-footer">
      
        <div id="donation_div"></div>


<script src="/qingfeng-blog/js/vdonate.js"></script>

<script>
var a = new Donate({
  title: '如果觉得我的文章对您有用，请随意打赏。您的支持将鼓励我继续创作!', // 可选参数，打赏标题
  btnText: 'Donate', // 可选参数，打赏按钮文字
  el: document.getElementById('donation_div'),
  wechatImage: 'http://img.lindaifeng.vip/typora-picgo-tuchaung/20230428150832.png',
  alipayImage: 'http://img.lindaifeng.vip/typora-picgo-tuchaung/20230428150823.png'
});
</script>
      
      
      <div>
        <ul class="post-copyright">
          <li class="post-copyright-author">
          <strong>Post author:  </strong>清峰</a>
          </li>
          <li class="post-copyright-link">
          <strong>Post link:  </strong>
          <a href="/qingfeng-blog/2021/02/08/Mysql/Linux安装mysql主从模式/" target="_blank" title="Linux安装mysql主从模式">http://example.com/2021/02/08/Mysql/Linux安装mysql主从模式/</a>
          </li>
          <li class="post-copyright-license">
            <strong>Copyright Notice:   </strong>
            All articles in this blog are licensed under <a rel="license" href="https://creativecommons.org/licenses/by-nc-nd/4.0/" target="_blank" title="Attribution-NonCommercial-NoDerivatives 4.0 International (CC BY-NC-ND 4.0)">CC BY-NC-ND 4.0</a>
            unless stating additionally.
          </li>
         
        </ul>
<div>

      
      
        
	<div id="comment">
		<!-- 来必力City版安装代码 -->
		<div id="lv-container" data-id="city" data-uid="MTAyMC8yOTQ4MS82MDQ5">
		<script type="text/javascript">
		   (function(d, s) {
		       var j, e = d.getElementsByTagName(s)[0];

		       if (typeof LivereTower === 'function') { return; }

		       j = d.createElement(s);
		       j.src = 'https://cdn-city.livere.com/js/embed.dist.js';
		       j.async = true;

		       e.parentNode.insertBefore(j, e);
		   })(document, 'script');
		</script>
		<noscript>为正常使用来必力评论功能请激活JavaScript</noscript>
		</div>
		<!-- City版安装代码已完成 -->
	</div>



      
      
        
  <ul class="article-tag-list" itemprop="keywords"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/qingfeng-blog/tags/mysql/" rel="tag">mysql</a></li></ul>

      

    </footer>
  </div>
</div>
  
    
<nav id="article-nav">
  
    <a href="/qingfeng-blog/2021/05/23/%E8%BF%90%E7%BB%B4%E5%AE%89%E8%A3%85/Linux%E5%AE%89%E8%A3%85nginx/" id="article-nav-newer" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Newer</strong>
      <div class="article-nav-title">
        
          Linux安装nginx
        
      </div>
    </a>
  
  
    <a href="/qingfeng-blog/2021/02/08/Mysql/Mysql%E9%94%99%E8%AF%AF%E7%A0%81/" id="article-nav-older" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Older</strong>
      <div class="article-nav-title">Mysql错误码</div>
    </a>
  
</nav>

  
</article>

<!-- Table of Contents -->

  <aside id="toc-sidebar">
    <div id="toc" class="toc-article">
    <strong class="toc-title">Contents</strong>
    
        <ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#Mysql-%E5%8F%8C%E4%B8%BB%E4%BB%8E-%E5%B7%B2%E6%B5%8B%E8%AF%95"><span class="nav-number">1.</span> <span class="nav-text">Mysql 双主从(已测试)</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E4%B8%80%E3%80%81%E6%B7%BB%E5%8A%A0%E9%85%8D%E7%BD%AE%E6%96%87%E4%BB%B6"><span class="nav-number">1.1.</span> <span class="nav-text">一、添加配置文件</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E4%BA%8C%E3%80%81%E5%88%9B%E5%BB%BA%E7%94%A8%E6%88%B7"><span class="nav-number">1.2.</span> <span class="nav-text">二、创建用户</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E4%B8%89%E3%80%81%E5%88%86%E5%88%AB%E7%BB%99%E7%94%A8%E6%88%B7%E6%8E%88%E4%BA%88%E5%A4%8D%E5%88%B6%E6%9D%83%E9%99%90"><span class="nav-number">1.3.</span> <span class="nav-text">三、分别给用户授予复制权限</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%9B%9B%E3%80%81%E5%88%86%E5%88%AB%E9%87%8D%E5%90%AF%E6%9C%8D%E5%8A%A1"><span class="nav-number">1.4.</span> <span class="nav-text">四、分别重启服务</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E4%BA%94%E3%80%81%E9%85%8D%E7%BD%AE%E4%B8%BB%E4%BB%8E%E6%9C%BA"><span class="nav-number">1.5.</span> <span class="nav-text">五、配置主从机</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E4%B8%83%E3%80%81%E5%88%86%E5%88%AB%E5%88%A9%E7%94%A8%E5%91%BD%E4%BB%A4%E5%90%AF%E5%8A%A8slave"><span class="nav-number">1.6.</span> <span class="nav-text">七、分别利用命令启动slave</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%85%AB%E3%80%81%E6%B5%8B%E8%AF%95%E7%BB%93%E6%9E%9C"><span class="nav-number">1.7.</span> <span class="nav-text">八、测试结果</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#Mysql-%E5%8D%95%E4%B8%BB%E4%BB%8E%EF%BC%88%E6%AD%A5%E9%AA%A4%E6%9C%89%E9%97%AE%E9%A2%98%EF%BC%89"><span class="nav-number">2.</span> <span class="nav-text">Mysql 单主从（步骤有问题）</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E4%B8%80%E3%80%81%E6%B7%BB%E5%8A%A0%E9%85%8D%E7%BD%AE%E6%96%87%E4%BB%B6-1"><span class="nav-number">2.1.</span> <span class="nav-text">一、添加配置文件</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E4%BA%8C%E3%80%81%E5%88%9B%E5%BB%BA%E7%94%A8%E6%88%B7-1"><span class="nav-number">2.2.</span> <span class="nav-text">二、创建用户</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E4%B8%89%E3%80%81%E5%88%86%E5%88%AB%E7%BB%99%E7%94%A8%E6%88%B7%E6%8E%88%E4%BA%88%E5%A4%8D%E5%88%B6%E6%9D%83%E9%99%90-1"><span class="nav-number">2.3.</span> <span class="nav-text">三、分别给用户授予复制权限</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%9B%9B%E3%80%81%E5%88%86%E5%88%AB%E9%87%8D%E5%90%AF%E6%9C%8D%E5%8A%A1-1"><span class="nav-number">2.4.</span> <span class="nav-text">四、分别重启服务</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E4%BA%94%E3%80%81%E9%85%8D%E7%BD%AE%E4%BB%8E%E6%9C%BA"><span class="nav-number">2.5.</span> <span class="nav-text">五、配置从机</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E4%B8%83%E3%80%81%E5%88%A9%E7%94%A8%E5%91%BD%E4%BB%A4%E5%90%AF%E5%8A%A8slave"><span class="nav-number">2.6.</span> <span class="nav-text">七、利用命令启动slave</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%85%AB%E3%80%81%E6%B5%8B%E8%AF%95%E7%BB%93%E6%9E%9C-1"><span class="nav-number">2.7.</span> <span class="nav-text">八、测试结果</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%A6%82%E6%9E%9C%E5%87%BA%E7%8E%B0Slave-IO-Running-No%E7%9A%84%E6%9C%BA%E5%99%A8%E4%B8%8A%E6%93%8D%E4%BD%9C%EF%BC%9A"><span class="nav-number">3.</span> <span class="nav-text">如果出现Slave_IO_Running: No的机器上操作：</span></a></li></ol>
    
    </div>
  </aside>


</section>
        
      </div>
      
      <footer id="footer">
  

  <div class="container">
      	<div class="row">
	      <!-- <p> Powered by <a href="http://hexo.io/" target="_blank">Hexo</a> and <a href="https://github.com/iTimeTraveler/hexo-theme-hiker" target="_blank">Hexo-theme-hiker</a> </p> -->
	      <p>May there be no bug in the world！</p>
        <p id="copyRightEn">Copyright &copy; 2022 - 2023 All Rights Reserved.</p>
	      
	      
    		<p class="busuanzi_uv">
				UV : <span id="busuanzi_value_site_uv"></span> |  
				PV : <span id="busuanzi_value_site_pv"></span>
		    </p>
  		   
		</div>

		
  </div>
</footer>


<!-- min height -->

<script>
    var wrapdiv = document.getElementById("wrap");
    var contentdiv = document.getElementById("content");
    var allheader = document.getElementById("allheader");

    wrapdiv.style.minHeight = document.body.offsetHeight + "px";
    if (allheader != null) {
      contentdiv.style.minHeight = document.body.offsetHeight - allheader.offsetHeight - document.getElementById("footer").offsetHeight + "px";
    } else {
      contentdiv.style.minHeight = document.body.offsetHeight - document.getElementById("footer").offsetHeight + "px";
    }
</script>
    </div>
    <!-- <nav id="mobile-nav">
  
    <a href="/qingfeng-blog/" class="mobile-nav-link">首页</a>
  
    <a href="/qingfeng-blog/categories" class="mobile-nav-link">分类</a>
  
    <a href="/qingfeng-blog/tags" class="mobile-nav-link">标签</a>
  
</nav> -->
    

<!-- mathjax config similar to math.stackexchange -->

<script type="text/x-mathjax-config">
  MathJax.Hub.Config({
    tex2jax: {
      inlineMath: [ ['$','$'], ["\\(","\\)"] ],
      processEscapes: true
    }
  });
</script>

<script type="text/x-mathjax-config">
    MathJax.Hub.Config({
      tex2jax: {
        skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code']
      }
    });
</script>

<script type="text/x-mathjax-config">
    MathJax.Hub.Queue(function() {
        var all = MathJax.Hub.getAllJax(), i;
        for(i=0; i < all.length; i += 1) {
            all[i].SourceElement().parentNode.className += ' has-jax';
        }
    });
</script>

<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.1/MathJax.js?config=TeX-AMS-MML_HTMLorMML">
</script>


  
<link rel="stylesheet" href="/qingfeng-blog/fancybox/jquery.fancybox.css">

  
<script src="/qingfeng-blog/fancybox/jquery.fancybox.pack.js"></script>




<script src="/qingfeng-blog/js/scripts.js"></script>



  
<script src="/qingfeng-blog/js/home.js"></script>




  
<script src="/qingfeng-blog/js/dialog.js"></script>









	<div style="display: none;">
    <script src="https://s95.cnzz.com/z_stat.php?id=1260716016&web_id=1260716016" language="JavaScript"></script>
  </div>



	<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js">
	</script>






  </div>

  <div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true" style="display: none;">
  <div class="modal-dialog">
    <div class="modal-content">
      <div class="modal-header">
        <h2 class="modal-title" id="myModalLabel">设置</h2>
      </div>
      <hr style="margin-top:0px; margin-bottom:0px; width:80%; border-top: 3px solid #000;">
      <hr style="margin-top:2px; margin-bottom:0px; width:80%; border-top: 1px solid #000;">


      <div class="modal-body">
          <div style="margin:6px;">
            <a data-toggle="collapse" data-parent="#accordion" href="#collapseOne" onclick="javascript:setFontSize();" aria-expanded="true" aria-controls="collapseOne">
              正文字号大小
            </a>
          </div>
          <div id="collapseOne" class="panel-collapse collapse" role="tabpanel" aria-labelledby="headingOne">
          <div class="panel-body">
            您已调整页面字体大小
          </div>
        </div>
      


          <div style="margin:6px;">
            <a data-toggle="collapse" data-parent="#accordion" href="#collapseTwo" onclick="javascript:setBackground();" aria-expanded="true" aria-controls="collapseTwo">
              夜间护眼模式
            </a>
        </div>
          <div id="collapseTwo" class="panel-collapse collapse" role="tabpanel" aria-labelledby="headingTwo">
          <div class="panel-body">
            夜间模式已经开启，再次单击按钮即可关闭 
          </div>
        </div>

        <div>
            <a data-toggle="collapse" data-parent="#accordion" href="#collapseThree" aria-expanded="true" aria-controls="collapseThree">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;关 于&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</a>
        </div>
         <div id="collapseThree" class="panel-collapse collapse" role="tabpanel" aria-labelledby="headingThree">
          <div class="panel-body">
            清峰小栈
          </div>
          <div class="panel-body">
            Copyright © 2023 清峰 All Rights Reserved.
          </div>
        </div>
      </div>


      <hr style="margin-top:0px; margin-bottom:0px; width:80%; border-top: 1px solid #000;">
      <hr style="margin-top:2px; margin-bottom:0px; width:80%; border-top: 3px solid #000;">
      <div class="modal-footer">
        <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>
      </div>
    </div>
  </div>
</div>
  
  <a id="rocket" href="#top" class=""></a>
  <script type="text/javascript" src="/qingfeng-blog/js/totop.js?v=1.0.0" async=""></script>
  
    <a id="menu-switch"><i class="fa fa-bars fa-lg"></i></a>
  
</body>
</html>